package day03;

import org.junit.Test;

import java.sql.*;

public class Demo01 {
    @Test
    public void insert () throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/shop";
        String user = "root";
        String password = "123456";
        Connection connection = DriverManager.getConnection(url,user,password);
        System.out.println("connection = " + connection);
        String sql = "insert into user (username,password) values ('tom','111')";
        Statement statement = connection.createStatement();
        statement.executeUpdate(sql);
        statement.close();
        connection.close();
    }
    @Test
    public void delete() throws Exception {
        //1.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");

        //2.获取连接
        String url = "jdbc:mysql://localhost:3306/shop";
        String user = "root";
        String password = "123456";
        Connection connection = DriverManager.getConnection(url,user,password);

        //3.准备sql
        String sql = "delete from user where uid = 1";

        /*
            4.获取执行平台->Statement对象
         */
        Statement statement = connection.createStatement();

        /*
            5.执行sql语句:Statement中的方法:
               int executeUpdate(String sql) ->  专门操作增删改的 ,不用处理结果集
        */
        statement.executeUpdate(sql);

        //6.关闭资源
        statement.close();
        connection.close();
    }
    @Test
    public void update() throws Exception {
        //1.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");

        //2.获取连接
        String url = "jdbc:mysql://localhost:3306/shop";
        String user = "root";
        String password = "123456";
        Connection connection = DriverManager.getConnection(url,user,password);

        //3.准备sql
        String sql = "update user set password = '666' where uid = 2";

        /*
            4.获取执行平台->Statement对象
         */
        Statement statement = connection.createStatement();

        /*
            5.执行sql语句:Statement中的方法:
               int executeUpdate(String sql) ->  专门操作增删改的 ,不用处理结果集
        */
        statement.executeUpdate(sql);

        //6.关闭资源
        statement.close();
        connection.close();
    }
    @Test
    public void query() throws Exception {
        //1.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");

        //2.获取连接
        String url = "jdbc:mysql://localhost:3306/shop";
        String user = "root";
        String password = "123456";
        Connection connection = DriverManager.getConnection(url, user, password);

        //3.准备sql
        String sql = "select * from user";

         /*
            4.获取执行平台->Statement对象
         */
        Statement statement = connection.createStatement();

        //5.执行sql语句:Statement中的方法:
        ResultSet resultSet = statement.executeQuery(sql);

        //6.处理结果集
        while (resultSet.next()) {
            int uid = resultSet.getInt("uid");
            String username = resultSet.getString("username");
            String pwd = resultSet.getString("password");
            System.out.println(uid + " " + username + " " + pwd);
        }
        //7.关闭资源
        resultSet.close();
        statement.close();
        connection.close();
    }
}
